[dbo].[BAEDisplayGetSelectiveContent]
create procedure [dbo].[BAEDisplayGetSelectiveContent] @pCategoryID varchar(8),
@pResultsRequested varchar(8),
@pHotContent varchar(20) as
DECLARE @SQLquery AS Nvarchar(2000)
SET @SQLquery = N'SELECT Top ' + @pResultsRequested + 'vCo.*, vCa.Title AS "CategoryTitle"'
SET @SQLquery = @SQLquery + N'FROM VimCategory vCa INNER JOIN VimCategoryLookup vCaL ON vCa.CategoryID = vCaL.CategoryID FULL OUTER JOIN VimContent vCo ON vCaL.ContentID = vCo.ContentID '
SET @SQLquery = @SQLquery + N' WHERE (vCo.IsPreview = 0) AND (vCo.IsPublished = 1) AND (vCo.IsDeleted = 0)
AND (vCo.PostDate <= GETDATE())
AND (vCo.RemoveDate >= GETDATE() OR vCo.RemoveDate IS NULL)
AND (vCo.ArchiveDate >= GETDATE() OR vCo.ArchiveDate IS NULL)
AND (vCo.ApprovalState = ''approved'') AND (vCaL.CategoryID = '+@pCategoryID+' ) '+@pHotContent+'
ORDER BY vCo.OrderPosition, vCo.PostDate, vCo.Title'
execute sp_executesql @SQLquery
GO
